/* ExcelInoutToDB.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-20	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.imp;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import system.base.ClientDownupfileInfo;
import system.base.InSub;
import system.base.InoutType;
import system.base.ServerDownupfileInfo;
import system.base.WarehouseCategory;
import framework.core.*;
import framework.core.DateUtil;

public class ExcelFileToDB implements GWBaseProcesser {
    
    
    public static String saveExcelFileToDB(DataInputStream aFile, DBConnection con,String strFilePath,String tableName,String warehouseCode)
    {
        String mReturn = "";;
        try{

            mReturn = saveExcelFile(aFile,con,tableName,warehouseCode);
            
            
            //在服务器上传下载表中记录操作信息
            ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
            sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
            sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
            sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
            sdfi.setDownloadtyp(ServerDownupfileInfo.DOWNLOADTYPE_UP);
            sdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString() );
            sdfi.setLastUpdateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
            sdfi.setLastUpdateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
            sdfi.setParmsstr(strFilePath);
            sdfi.setDatatableName(tableName);
            sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
            sdfi.setStatus(ConstDefine.STR_YES);
            sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
            sdfi.setWarehouseUincode(warehouseCode);
            sdfi.save(con);         
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
            strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
            try {
                //在服务器上传下载表中记录操作信息
                ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
                sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
                sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
                sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
                sdfi.setDownloadtyp(ServerDownupfileInfo.DOWNLOADTYPE_UP);
                sdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString());
                sdfi.setLastUpdateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
                sdfi.setLastUpdateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
                sdfi.setParmsstr(strFilePath);
                sdfi.setDatatableName(tableName);
                sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
                sdfi.setStatus(ConstDefine.STR_YES);
                sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
                sdfi.setWarehouseUincode(warehouseCode);
                sdfi.save(con); 
            } catch (Exception e1) {
                //记录到日志文件
                
            }  
        }
        return mReturn;
    }
    
    //保存Excel文件
    private static String saveExcelFile(DataInputStream aFile,DBConnection con,String tableName,String warehouseCode) throws Exception{
        
        String mReturn = "";
        String strTableName = tableName;
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook = new HSSFWorkbook(aFile);
        //DBConnection con = null;
        DBPreparedStatement pst = null;
        
        int sheets = workbook.getNumberOfSheets();
        for(int intSheet = 0;intSheet<sheets;intSheet++)
        {  
            //HSSFSheet sheet = workbook.getSheetAt(workbook.getSheetIndex(strTableName));
            HSSFSheet sheet = workbook.getSheetAt(intSheet);
            strTableName = workbook.getSheetName(intSheet);
            
            int ColumnCount=0;
            int rownum = 0;
            int connectNum = 0;
            try{
                HSSFRow tHSSFFirst = sheet.getRow(0);
                ColumnCount = tHSSFFirst.getLastCellNum()+1;
                
                while (true) {
                    //if(connectNum == 0){
                    //    con = DBConnection.open();
                    //    con.setAutoCommit(false);
                    //}    
                    HSSFRow tHSSFRow = sheet.getRow(1 + rownum);
                    //if(rownum == 200){
                    //    System.out.println(rownum);
                    //}
                    boolean flag = false;
                    try {
    
                        for (int j = 0; j < ColumnCount; j++) {
                            if (tHSSFRow == null) {
                                break;
                            }
                            if (tHSSFRow.getCell((short) j) == null) {
                                break;
                            }
                            flag = true;
                            break;
                        }
                        if (!flag) {
                            break;
                        } else {
                            //解析Excel文件开始
                            String sql = "";
                            String sqlCount = "select count(*) from " + strTableName ;
                            String strId =  PoiUtil.getCellValue(tHSSFRow, 0, "--1");
                            sqlCount += " where ID='" + strId + "'";
                            if (!strId.equals("--1"))
                            {
                                int dataCount = DBEngine.doCount(con, sqlCount);
                                if ( dataCount>0 )
                                {
                                    sql = "update " + strTableName + " set "; 
                                    for(int j=1;j<ColumnCount;j++)
                                    {
                                        String strVal =  PoiUtil.getCellValue(tHSSFRow, j, "");
                                        String strField = PoiUtil.getCellValue(tHSSFFirst, j, "");
                                        if (!strField.equals(""))
                                        {
                                            sql += strField + "='" + StringUtil.replaceSql(strVal) +"'"; 
                                            if (j+1 != ColumnCount)
                                            {
                                                sql += ",";                                    
                                            }
                                        }
                                    }
                                    sql += " where id='" + strId +"'";
                                }
                                else
                                {
                                    sql ="insert into " + strTableName +"(";
                                    for(int j=0;j<ColumnCount;j++)
                                    {
                                        String strField = PoiUtil.getCellValue(tHSSFFirst, j, "");
                                        sql += strField;
                                        if (j+1 != ColumnCount) sql += ",";
                                    }
                                    sql += ")";
                                    sql += " values(";
                                    for(int j=0;j<ColumnCount;j++)
                                    {
                                        String strVal =  PoiUtil.getCellValue(tHSSFRow, j, "");
                                        String strField = PoiUtil.getCellValue(tHSSFFirst, j, "");
                                        if (!strField.equals(""))
                                        {
                                            sql += "'" + StringUtil.replaceSql(strVal) + "'";  
                                            if (j+1 != ColumnCount)
                                            {
                                                sql += ",";                                    
                                            }
                                        }    
                                    }
                                    sql += ")";
                                }
                            }
                            rownum ++;
                            connectNum++;
                            //System.out.println("upload Excel sql:" + sql);
                            pst = con.prepareStatement(sql);
                            pst.executeUpdate();
                            pst.close();
                            pst = null;
                            
                            //if(connectNum > 200){
                            //    con.commit();
                            //    con.close();
                            //    con = null;
                            //    connectNum = 0;
                            //}
                        }
                    }catch(Exception e)
                    {
                        e.printStackTrace();
                        con.rollback();
                        return  new String("不能保存: <"+ strTableName+"> Excel文件，请稍后重试！");
                    }
                }
                
                //if(con != null){
                //    con.commit();
                    //con.close();
                    //con = null;
                //}    
            }catch(Exception e){
                System.out.println(e.getMessage());
                con.rollback();
                throw new BizException("不能保存: <"+ strTableName+"> Excel文件，请稍后重试！");
            }
            finally{
                if(pst != null){
                    pst.close();
                    pst = null;
                }
                //if(con != null){
                 //   con.close();
                   // con = null;
                //}
            }
        }
        return mReturn;
    } 
    
}
